#!/usr/bin/env python
# -*- coding:utf-8 -*-
from datetime import datetime

from conn.MyLogs import MyLogs
from excel.PruTuutDb import PruTuutDb
from members.beans.MemberRewardStrategyObj import MemberRewardStrategyObj


class MemberRewardStrategyService:
    std_array = {}
    conn = None

    def __init__(self, conn):
        self.conn = conn

    def fetch(self, param):
        try:
            count_sql = f" SELECT count(`id`) FROM `mem_member_reward_strategy` WHERE 1=1 %s"
            fetch_sql = f" SELECT {MemberRewardStrategyObj.to_string(True)}" \
                        f" FROM `mem_member_reward_strategy` WHERE 1=1 %s "
            where_sql = ""
            if len(param['key']) > 0:
                where_sql = where_sql + f" AND CONCAT(`name`,`diff_url`) like CONCAT('%',\'{param['key']}\','%') "

            int_begin = 0
            if int(param['pageNum']) > 1:
                int_begin = (int(param['pageNum']) - 1) * int(param['pageSize'])
            elif int(param['pageNum']) == 1:
                int_begin = 0 * int(param['pageSize'])

            where_sql_no_limit = where_sql
            where_sql_limit = where_sql_no_limit + f" LIMIT {int_begin}, {int(param['pageSize'])}"
            data_total = self.conn.sql_fetch(count_sql % where_sql_no_limit)
            data_list = self.conn.sql_fetch(fetch_sql % where_sql_limit)
            items = []
            if len(data_list) > 0:
                items = [MemberRewardStrategyObj.load_db_2_dict(data=item) for item in data_list]
            return {"code": 200, "data": items, "total": data_total[0][0]}
        except Exception as e:
            MyLogs.logger.error("ERROR: %s" % e)
        return {"code": 500, "data": None}

    def fetch_one(self, id):
        fetch_sql = f"SELECT {MemberRewardStrategyObj.to_string(True)} FROM `mem_member_reward_strategy` WHERE 1 = 1 " \
                    f" AND `id` = \'{id}\'"
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                res = [MemberRewardStrategyObj.load_db_2_dict(item) for item in items]
                return {"code": 200, "data": res}
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}

    # 加入了事务控制，优先注册会员，然后修改会员等级
    def add_strategy(self, strategy: object):
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            diff_url = strategy['diff_url']
            add_check = self.count_diff_url(diff_url=diff_url)
            if add_check:
                return {"code": 203, "data": None}
            # print(json.dumps(member, sort_keys=True, ensure_ascii=False, indent=4))
            insert_sql = f" INSERT INTO `mem_member_reward_strategy` ({MemberRewardStrategyObj.to_string(False)}) " \
                         " VALUES ( %s )" % MemberRewardStrategyObj.concat_datas(strategy)
            cursor.execute(insert_sql)
            pruTuut_conn.commit()
            return {"code": 200, "data": None}
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 500, "data": None}

    def update_strategy(self, strategy, id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            diff_url = strategy['diff_url']
            add_check = self.count_diff_url(diff_url=diff_url)
            if not add_check:
                return {"code": 404, "data": None}
            old_strategy = self.fetch_one(id=id)
            update_str = MemberRewardStrategyObj.split_params(strategy)
            update_sql = f" UPDATE `mem_member_reward_strategy` SET %s, `create_date`=\'{current_time}\'" \
                         f" WHERE `id`= \'%s\'" % (update_str, id)
            cursor.execute(update_sql)
            pruTuut_conn.commit()
            return {"code": 200, "data": None}
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 500, "data": None}

    def drop_strategy(self, id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(id=id)
        if not add_check:
            return {"code": 404, "data": None}
        old_data = self.fetch_one(id=id)
        try:
            pruTuut_conn = PruTuutDb.get_conn()
            # 开始配置事务
            pruTuut_conn.begin()
            cursor = pruTuut_conn.cursor()
            execute_SQL = f" DELETE FROM `mem_member_reward_strategy` WHERE `id` = \'{old_data['data'][0]['id']}\' "
            cursor.execute(execute_SQL)
            pruTuut_conn.commit()
        except Exception as e:
            pruTuut_conn.rollback()
            print(e)
        finally:
            pruTuut_conn.close()
        return {"code": 200, "data": None}

    def res_strategy(self, id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(id=id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member_reward_strategy` SET " \
                      f" `STATUS` = \'{MemberRewardStrategyObj.STRATEGY_STATUS['INUSE']}\', `create_date`=\'{current_time}\' " \
                      f" WHERE `id` = \'{id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def act_strategy(self, id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(id=id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member_reward_strategy` SET " \
                      f" `STATUS` = \'{MemberRewardStrategyObj.STRATEGY_STATUS['INUSE']}\', `create_date`=\'{current_time}\' " \
                      f" WHERE `id` = \'{id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def stop_strategy(self, id):
        # 获取当前时间
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        add_check = self.count_one(id=id)
        if not add_check:
            return {"code": 404, "data": None}
        execute_SQL = f" UPDATE `mem_member_reward_strategy` SET " \
                      f" `STATUS` = \'{MemberRewardStrategyObj.STRATEGY_STATUS['STOP']}\', `create_date`=\'{current_time}\' " \
                      f" WHERE `id` = \'{id}\'"
        self.conn.sql_commit(execute_SQL)
        return {"code": 200, "data": None}

    def count_diff_url(self, diff_url) -> bool:
        fetch_sql = f"SELECT count(`id`) FROM `mem_member_reward_strategy` WHERE 1 = 1 " \
                    f" AND `diff_url` = \'{diff_url}\'"
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                return items[0][0] > 0
            return False
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}

    def count_one(self, id) -> bool:
        fetch_sql = f"SELECT count(`id`) FROM `mem_member_reward_strategy` WHERE 1 = 1 " \
                    f" AND `id` = \'{id}\'"
        try:
            items = self.conn.sql_fetch(fetch_sql)
            if len(items) > 0:
                return items[0][0] > 0
            return False
        except Exception as e:
            print(e)
        return {"code": 500, "data": None}
